﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetUserSessionLog')
	BEGIN
		DROP  Procedure  [esf_sso].P_GetUserSessionLog
	END

GO

CREATE Procedure [esf_sso].P_GetUserSessionLog
	@pUserAlias			VARCHAR (20) = NULL
	,@pStartDateTime	DATETIME = NULL
	,@pEndDateTime		DATETIME = NULL
	,@pPageIndex		BIGINT = 0
	,@pPageSize			BIGINT = 30
AS

	SELECT * FROM
		(SELECT
			ROW_NUMBER () OVER (ORDER BY USL.StartDateTime) AS Rxi
			,USL.* 
			,U.Alias AS Usr_Alias
		FROM 
			[esf_sso].UserSessionLog USL
		INNER JOIN [esf_sso].[User] U
			ON U.PersonID = USL.UserPersonID
		WHERE
			(@pStartDateTime IS NULL OR USL.StartDateTime >= @pStartDateTime)
			AND (@pEndDateTime IS NULL OR USL.EndDateTime <= @pEndDateTime)
			AND (@pUserAlias IS NULL OR U.Alias = @pUserAlias)) Tx
	WHERE
		Tx.Rxi BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	ORDER BY 
		Tx.Rxi

GO